const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

console.log('🔍 验证实例表字段前缀命名规则');
console.log('=====================================');

try {
  const targetTable = 'device_instances_piping_cooling_water_spray_system';
  
  // 1. 获取实例表字段
  const instanceTableInfo = db.prepare(`PRAGMA table_info(${targetTable})`).all();
  const systemFields = ['id', 'created_at', 'updated_at'];
  const dataFields = instanceTableInfo.filter(col => !systemFields.includes(col.name));
  
  console.log(`📊 实例表总字段数: ${dataFields.length}`);
  
  // 2. 根据前缀分类字段
  const fieldsByPrefix = {
    'naming_': [],
    'position_': [],
    'base_params_': [],
    'other': []
  };
  
  dataFields.forEach(field => {
    if (field.name.startsWith('naming_')) {
      fieldsByPrefix['naming_'].push(field.name.replace('naming_', ''));
    } else if (field.name.startsWith('position_')) {
      fieldsByPrefix['position_'].push(field.name.replace('position_', ''));
    } else if (field.name.startsWith('base_params_')) {
      fieldsByPrefix['base_params_'].push(field.name.replace('base_params_', ''));
    } else {
      fieldsByPrefix['other'].push(field.name);
    }
  });
  
  console.log('\n📋 按前缀分类的字段:');
  Object.entries(fieldsByPrefix).forEach(([prefix, fields]) => {
    console.log(`  ${prefix}: ${fields.length}个字段`);
    fields.forEach((field, index) => {
      console.log(`    ${index + 1}. ${field}`);
    });
  });
  
  // 3. 验证与实际属性表的匹配
  console.log('\n🔍 验证与属性表的匹配:');
  
  // 获取实际使用的属性表 (从设备类型映射)
  const deviceMapping = db.prepare(`
    SELECT naming_table, position_table, base_params_table 
    FROM device_type_mappings 
    WHERE classification_name = '冷却水喷淋装置'
  `).get();
  
  if (deviceMapping) {
    console.log('✅ 设备类型映射:');
    console.log(`  命名表: ${deviceMapping.naming_table}`);
    console.log(`  位置表: ${deviceMapping.position_table}`);
    console.log(`  基础参数表: ${deviceMapping.base_params_table}`);
    
    // 验证每个属性表的字段匹配
    const attributeTables = [
      {name: deviceMapping.naming_table, prefix: 'naming_', fields: fieldsByPrefix['naming_']},
      {name: deviceMapping.position_table, prefix: 'position_', fields: fieldsByPrefix['position_']},
      {name: deviceMapping.base_params_table, prefix: 'base_params_', fields: fieldsByPrefix['base_params_']}
    ];
    
    console.log('\n📊 字段匹配验证:');
    
    attributeTables.forEach(table => {
      try {
        const tableInfo = db.prepare(`PRAGMA table_info(${table.name})`).all();
        const actualFields = tableInfo.filter(col => !systemFields.includes(col.name));
        
        console.log(`\n  ${table.name} (${table.prefix}):`);
        console.log(`    属性表字段数: ${actualFields.length}`);
        console.log(`    实例表字段数: ${table.fields.length}`);
        
        // 逐字段匹配
        let matchCount = 0;
        actualFields.forEach(field => {
          const inInstance = table.fields.includes(field.name);
          if (inInstance) matchCount++;
          console.log(`      ${field.name}: ${inInstance ? '✅' : '❌'}`);
        });
        
        console.log(`    匹配率: ${(matchCount / actualFields.length * 100).toFixed(1)}%`);
        
        // 查找不匹配的字段
        const extraInInstance = table.fields.filter(name => !actualFields.some(f => f.name === name));
        const missingInInstance = actualFields.filter(field => !table.fields.includes(field.name));
        
        if (extraInInstance.length > 0) {
          console.log(`    实例表多出: ${extraInInstance.join(', ')}`);
        }
        if (missingInInstance.length > 0) {
          console.log(`    实例表缺少: ${missingInInstance.map(f => f.name).join(', ')}`);
        }
        
      } catch (error) {
        console.log(`    ❌ 无法验证 ${table.name}: ${error.message}`);
      }
    });
    
  } else {
    console.log('❌ 未找到设备类型映射');
  }
  
  // 4. 总结实例表创建规则
  console.log('\n📐 实例表字段整合规则总结:');
  console.log('=====================================');
  console.log('🔹 实例表结构 = 系统字段 + 业务字段 + 属性字段');
  console.log('🔹 系统字段: id, created_at, updated_at');
  console.log('🔹 业务字段: device_type_code, instance_number, object_number, object_description');
  console.log('🔹 属性字段: 前缀命名规则');
  console.log('   • naming_[字段名] ← 来自命名表字段');
  console.log('   • position_[字段名] ← 来自位置表字段');
  console.log('   • base_params_[字段名] ← 来自基础参数表字段');
  
  console.log('\n🎯 字段映射公式:');
  console.log('实例表字段名 = 前缀 + 原始属性表字段名');
  console.log('例如:');
  console.log('  table_c2.设计物资编码 → naming_设计物资编码');
  console.log('  table_c6.工厂地址 → position_工厂地址');
  console.log('  table_c494.规格 → base_params_规格');
  
  const totalAttributeFields = fieldsByPrefix['naming_'].length + fieldsByPrefix['position_'].length + fieldsByPrefix['base_params_'].length;
  const totalBusinessFields = fieldsByPrefix['other'].length;
  
  console.log('\n📈 字段统计:');
  console.log(`  业务字段: ${totalBusinessFields}个`);
  console.log(`  属性字段: ${totalAttributeFields}个`);
  console.log(`  系统字段: 3个`);
  console.log(`  总计: ${totalBusinessFields + totalAttributeFields + 3}个`);
  
} catch (error) {
  console.error('❌ 验证失败:', error.message);
} finally {
  db.close();
}